Release 10.1A: OpenEdge Development:
ProDataSets

Creating a data access procedure for the Order ProDataSet

The first step is to create a data access procedure that handles all the code that requires knowledge of the data source. To do this, you create a procedure to act as the data access object for the dsOrder ProDataSet in the same way that CodeSource.p does for the code table ProDataSet from the previous chapter. Figure 10–1 illustrates the kinds of data definitions and logic to consider as part of the data access object.

Figure 10–1: Data access procedure

Figure 10–1 illustrates:

The data access object encapsulates all database references. Therefore, query definitions are part of the object.
Data-Source definitions that map database tables and queries to ProDataSet buffers are part of the object.
Methods (internal procedures or functions) that attach and detach Data-Sources for the ProDataSet buffers are part of the object.
Methods that prepare database queries or methods that in other ways reference database table and field names directly are part of the object. For example, one kind of request of the Order ProDataSet is to return all the data for a particular Order number. This request can be made of the higher-level business entity (as you’ll do later in this chapter), but the query itself should be prepared in the data access object. This is because that request requires defining a particular database query to get the right Order from the database.
FILL event logic that determines the final form of what is in the ProDataSet is part of the object. These FILL event procedures can be associated with the ProDataSet instance at the time of the ATTACH, as in these examples.

In this way, all the definitions and code that reference the database are nicely captured in a single place. Here they can be maintained, as needed, when database definitions or data sources change. All the higher levels of access to the ProDataSet don’t contain any such references, which isolates them from the specifics of the Data-Sources. Once the FILL event procedures have been associated with the caller’s ProDataSet instance and the Data-Sources attached to it, the caller can simply execute the FILL method, and all the required logic is executed properly on that instance.

So, let’s create the data access procedure OrderSource.p.

Much of the code in this procedure comes from the OrderEvents.p procedure in Chapter 7 "Advanced Events and Attributes". You can copy code from there and adapt it as needed. The purpose of this exercise is to begin to isolate the code better, based on what role it plays, to begin to provide more of an architecture to the application’s objects.

In this example, the data access procedure has a static definition of the ProDataSet and its temp-tables:

/* OrderSource.p -- Data-Sources and FILL events for Order ProDataSet */ {dsOrderTT.i} {dsOrder.i}

As we noted in Chapter 7, "Advanced Events and Attributes,", it can help you isolate your ProDataSet definitions better if you can avoid this, but if there are many references to ProDataSet tables and fields in the FILL logic or elsewhere, then this might not be practical. This example shows the alternative of having the definitions in the data access procedure so that they can be referenced in static 4GL statements. As we work through the procedure, it will be important to note how the actual ProDataSet instance the code is operating on is not the one that this procedure gets by including the definitions. The support code is always using the instance from the requesting procedure.

The top-level definition and the Data-Source definitions will be familiar from OrderEvents.p, as shown:

DEFINE QUERY qOrder FOR Order, Customer, SalesRep. DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder Order KEYS (OrderNum), Customer KEYS (CustNum), SalesRep KEYS (SalesRep). DEFINE DATA-SOURCE srcOline FOR OrderLine. DEFINE DATA-SOURCE srcItem FOR ITEM KEYS (ItemNum).

OrderEvents.p was intended to run as a stand-alone procedure with no internal procedures. Thus, it took the Order Number and OUTPUT ProDataSet as parameters directly. In this case, the data access procedure runs as a persistent procedure, so it has no parameters. Instead, there is an internal procedure, called fetchOrder, that implements this specific request for a single Order. Because it takes dsOrder as an INPUT-OUTPUT parameter, passed BY-REFERENCE, it is the caller’s instance of the ProDataSet that is used, not the one represented by the include files at the top of OrderSource.p.

The procedure uses the QUERY-PREPARE method to get the right order, and then fills the ProDataSet, as shown:

PROCEDURE fetchOrder: DEFINE INPUT PARAMETER piOrderNum AS INTEGER NO-UNDO. DEFINE INPUT-OUTPUT PARAMETER DATASET FOR dsOrder. QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum = " + STRING(piOrderNum) + ", FIRST Customer OF Order, FIRST SalesRep OF Order"). /* Note that this reference to dsOrder is not using the local definition but rather the actual dataset instance being passed in by reference. */ IF VALID-HANDLE(DATASET dsOrder:GET-BUFFER-HANDLE(1):DATA-SOURCE) THEN DATASET dsOrder:FILL(). ELSE DO: DATASET dsOrder:GET-BUFFER-HANDLE(1):TABLE-HANDLE:ERROR-STRING = "Data-Sources not attached". DATASET dsOrder:ERROR = TRUE. END. RETURN. END PROCEDURE. /* fetchOrder */

The calling procedure runs the attach method (defined later) before running fetchOrder, so that everything has been set up properly for the FILL. The code checks to make sure that there is a Data-Source for the top-level buffer before proceeding with the FILL. If not, it sets the ERROR attribute for the ProDataSet and an error message on the top-level temp-table, which the caller can inspect.

Later in this chapter, you’ll write another procedure that really represents the Order entity itself. This will have the ProDataSet instance that is actually used in the application, and it will define the API that other procedures, such as a client window, would use to access the ProDataSet. That API will include a fetchOrder procedure.

Why, then, is this version of fetchOrder here in the data access procedure? Since it needs to use a specific database query to prepare the top-level table, it is better to put the procedure into the data access object. The fetchOrder procedure in the Order entity itself will turn around and run this one to maintain the right level of encapsulation in the objects.

Two of the FILL event procedures from OrderEvents.p are preserved here, postOlineFill and postItemRowFill. Procedure postOlineFill calculates the OrderTotal in the ttOrder table, as shown:

PROCEDURE postOlineFill: DEFINE INPUT PARAMETER DATASET FOR dsOrder. DEFINE VARIABLE dTotal AS DECIMAL NO-UNDO. /* Here as well "ttOline" uses the local definition for compilation but points to the ttOline table in the input parameter at run time. */ FOR EACH ttOline WHERE ttOline.OrderNum = ttOrder.OrderNum: dTotal = dTotal + ttOline.ExtendedPrice. END. ttOrder.OrderTotal = dTotal. END PROCEDURE. /* postOlineFill */

Procedure postItemRowFill edits the ItemName field in the ttItem table, as shown:

PROCEDURE postItemRowFill: DEFINE INPUT PARAMETER DATASET FOR dsOrder. DEFINE VARIABLE iType AS INTEGER NO-UNDO. DEFINE VARIABLE cItemTypes AS CHARACTER NO-UNDO INIT "BASEBALL,CROQUET,FISHING,FOOTBALL,GOLF,SKI,SWIM,TENNIS". DEFINE VARIABLE iTypeNum AS INTEGER NO-UNDO. DEFINE VARIABLE cType AS CHARACTER NO-UNDO. DO iType = 1 TO NUM-ENTRIES(cItemTypes): cType = ENTRY(iType, cItemTypes). IF INDEX(ttItem.ItemName, cType) NE 0 THEN ttItem.ItemName = REPLACE(ttItem.ItemName, cType, cType). END. END PROCEDURE. /* postItemRowFill */

There’s something important to note about these two procedures. They contain direct references to fields such as ttItem.ItemName, which is possible because the temp-table definitions are included in the procedure. But remember that the local instance of the ProDataSet and its temp-tables is used for definition only. When Progress invokes these procedures during the FILL, it passes in the current ProDataSet instance implicitly BY-REFERENCE. Progress not only adjusts all references to the ProDataSet itself to point to that external ProDataSet, but also all references to its temp-tables and their fields. This gives you the best of both worlds, as it were: a local static definition that makes the code simpler and clearer, but an automatic reference at run time to an externally defined ProDataSet that is passed into the procedure without any copying or other overhead.

The code to attach the Data-Sources has been separated out, however, so that it can be executed not only for a FILL but also on a save. This is in the function attachDataSet, which also runs the SET-CALLBACK-PROCEDURE for the two FILL events, as shown:

FUNCTION attachDataSet RETURNS LOGICAL (INPUT phDataSet AS HANDLE): phDataSet:GET-BUFFER-HANDLE("ttOline"):SET-CALLBACK-PROCEDURE ("AFTER-FILL", "postOlineFill", THIS-PROCEDURE). phDataSet:GET-BUFFER-HANDLE("ttItem"):SET-CALLBACK-PROCEDURE ("AFTER-ROW-FILL", "postItemRowFill", THIS-PROCEDURE). phDataSet:GET-BUFFER-HANDLE("ttOrder"):ATTACH-DATA-SOURCE (DATA-SOURCE srcOrder:HANDLE, "Customer.Name,CustName"). phDataSet:GET-BUFFER-HANDLE("ttOline"):ATTACH-DATA-SOURCE (DATA-SOURCE srcOline:HANDLE). phDataSet:GET-BUFFER-HANDLE("ttItem"):ATTACH-DATA-SOURCE (DATA-SOURCE srcItem:HANDLE). END FUNCTION. /* attachDataSet */

There is also a corresponding detachDataSet function:

FUNCTION detachDataSet RETURNS logic (INPUT phDataSet AS HANDLE): DEFINE VARIABLE iBuff AS INTEGER NO-UNDO. DO iBuff = 1 TO DATASET dsOrder:NUM-BUFFERS: phDataSet:GET-BUFFER-HANDLE(iBuff):DETACH-DATA-SOURCE(). END. END FUNCTION. /* detachDataSet */